package org.compiere.process;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MPeriod;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

public class BPM_CashExecutionForm extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	private BigDecimal Period = null;
	private MPeriod mperiod = null;
	private BigDecimal Year = null;
	private boolean isThousand = false;
	
	private int iYear = 0;

	private static CLogger s_log = CLogger.getCLogger(BCS_ACReport.class);
	/** */
	private String path = "";

	private int AD_Org_ID = -1;
	private int AD_Client_ID = -1;
	
	HSSFWorkbook wb = null;
	String Mother = "";
	String Address = "";

	int Row1 = 0;
	int Row2 = 0;
	
	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("PeriodFrom")) {
				Period = (BigDecimal) para[i].getParameter();
			}else if (name.equals("C_Year_ID")) {
				Year = (BigDecimal) para[i].getParameter();
				iYear = Integer.parseInt(para[i].getInfo());
			}
			else if (name.equals("isThousand")) {
				isThousand = ((String) para[i].getParameter()).equals("Y");
			}
			else
				
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		// AD_Client_ID = getAD_Client_ID();
		// AD_Org_ID = getAD_Org_ID();

		path = Util.localFilePath;
		// / here another code)
	}

	@Override
	protected String doIt() throws Exception {

		
		String result = "";

		
//		if (period == null || companies.size() == 0)
//			return Msg.translate(m_ctx,
//					"По данным параметрам данные отсутствуют.");
	
		// ---------------------------------------------------------------
		// Write the output to a file
		
		String file = "BPM_CashExecutionForm.xlsx";
		String filePath = Util.localFilePath + file;
//		String file = Util.localFilePath +"test.xlsx";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file.substring(0,file.indexOf(".")));
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate"));

		InputStream inputStream = entry.getInputStream();  
		XSSFWorkbook wb = new XSSFWorkbook(inputStream);
		XSSFSheet sheet = wb.getSheetAt(0);

		
		DataFormat format = wb.createDataFormat();
		
		mperiod = new MPeriod(Env.getCtx(), Period.intValue(), null);
		
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(mperiod.getStartDate());
		int startDay =  calendar.get(calendar.DAY_OF_MONTH);
		calendar.setTime(mperiod.getEndDate());
		int endDay =  calendar.get(calendar.DAY_OF_MONTH);
		
		int Days = endDay - startDay;
		
		
		CellStyle csValue = wb.createCellStyle();
		csValue.setBorderBottom(CellStyle.BORDER_THIN);
		csValue.setBorderLeft(CellStyle.BORDER_THIN);
		csValue.setBorderRight(CellStyle.BORDER_THIN);
		csValue.setBorderTop(CellStyle.BORDER_THIN);
		csValue.setAlignment(CellStyle.ALIGN_RIGHT);
		if(!isThousand)
		csValue.setDataFormat(format.getFormat("#,##0.00"));
		
		
	    Font font = wb.createFont();
	    font.setFontHeightInPoints((short)10);
	    font.setFontName("Arial");
	    csValue.setFont(font);

//	    -----------------
		CellStyle csName = wb.createCellStyle();
		csName.setBorderBottom(CellStyle.BORDER_THIN);
		csName.setBorderLeft(CellStyle.BORDER_THIN);
		csName.setBorderRight(CellStyle.BORDER_THIN);
		csName.setBorderTop(CellStyle.BORDER_THIN);
		csName.setAlignment(CellStyle.ALIGN_LEFT);
		
		
	    Font fontNames = wb.createFont();
	    fontNames.setFontHeightInPoints((short)10);
	    fontNames.setFontName("Arial");
	    csName.setFont(fontNames);

//	    -----------------
		CellStyle csNameB = wb.createCellStyle();
		csNameB.setBorderBottom(CellStyle.BORDER_THIN);
		csNameB.setBorderLeft(CellStyle.BORDER_THIN);
		csNameB.setBorderRight(CellStyle.BORDER_THIN);
		csNameB.setBorderTop(CellStyle.BORDER_THIN);
		csNameB.setAlignment(CellStyle.ALIGN_LEFT);
		
		
	    Font fontNamesB = wb.createFont();
	    fontNamesB.setFontHeightInPoints((short)10);
	    fontNamesB.setFontName("Arial");
	    fontNamesB.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    csNameB.setFont(fontNamesB);

//		--------------------	    
		CellStyle csCaption = wb.createCellStyle();
		csCaption.setBorderBottom(CellStyle.BORDER_THIN);
		csCaption.setBorderLeft(CellStyle.BORDER_THIN);
		csCaption.setBorderRight(CellStyle.BORDER_THIN);
		csCaption.setBorderTop(CellStyle.BORDER_THIN);
		csCaption.setAlignment(CellStyle.ALIGN_CENTER);
		csCaption.setWrapText(true);
		
		
	    Font fontCaption = wb.createFont();
	    fontCaption.setFontHeightInPoints((short)11);
	    fontCaption.setFontName("Times New Roman");
	    fontCaption.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    csCaption.setFont(fontCaption);
	    
	    
	
		Row r = null;
		Cell c = null;
		int row = 9;
		r = sheet.getRow(row);
//		Cell cs = r.getCell(0);
//		sheet.shiftRows(1, 2, 3);
		
		for(int i=0; i <= Days; i++){
			c = r.createCell(i+1);
//			c.setCellStyle(cs.getCellStyle());
			c.setCellValue(i+1 + " день месяца");
			c.setCellStyle(csCaption);
			sheet.autoSizeColumn(i+1);
		}
		c = r.createCell(Days+2);
//		c.setCellStyle(cs.getCellStyle());
		c.setCellValue("   Итого   ");
		c.setCellStyle(csCaption);
		
		
		
		
		//--------------------------------------------- 1-----------------
		// bank deposit
		List<line> listBankDeposit = getBanksDeposit1(mperiod);
		if(listBankDeposit.size() > 0){
			row++;
			//caption
			r = sheet.getRow(row);
			c = getCell(r, 0);
			c.setCellValue("Денежные средства на депозитных счетах всего:");
			c.setCellStyle(csNameB);
			setRowStyle(sheet, Days, r, csNameB);
			row++;

			List<String> BanksName = new ArrayList<String>();
			for(int i=0; i < listBankDeposit.size(); i++){
				if(BanksName.indexOf(listBankDeposit.get(i).name) == -1)
					BanksName.add(listBankDeposit.get(i).name);
			}
			
			
			for(int j=0; j < BanksName.size(); j++){
						r = getRow(sheet, row);
						c = getCell(r, 0);
						c.setCellValue("  "+BanksName.get(j));
						c.setCellStyle(csName);

						
						Calendar ca = Calendar.getInstance();
						ca.setTime(mperiod.getStartDate());
						ca.add(ca.DATE, -1);
						BigDecimal summ = Env.ZERO;
						BigDecimal cash = Env.ZERO;
						
						int c_bank_id = 0;
						for(int i=0; i < listBankDeposit.size(); i++){
							if(BanksName.get(j).equals(listBankDeposit.get(i).name))
								c_bank_id = listBankDeposit.get(i).c_bank_id;
						}
						
						
						BigDecimal saldo = getBanksDepositSaldo1(mperiod, c_bank_id); 
						
						if(saldo.compareTo(Env.ZERO) == 0)
							continue;
						
						for(int d = 1; d <= Days+1; d++){
							for(int i=0; i < listBankDeposit.size(); i++){
								if(BanksName.get(j).equals(listBankDeposit.get(i).name) && listBankDeposit.get(i).dateacct.equals(ca.getTime())){
										c = getCell(r, d);
										if(d ==1) continue;
										saldo = saldo.add(listBankDeposit.get(i).sum);
										c.setCellValue(saldo.doubleValue());
										c.setCellStyle(csValue);
										summ = summ.add(saldo);
										cash = saldo;//listBankDeposit.get(i).sum;
								}else{
									c = getCell(r, d);
									c.setCellValue(saldo.doubleValue());
									c.setCellStyle(csValue);
								}
							}
							ca.add(Calendar.DATE, 1);
						}
						
						c = getCell(r, Days+2);
						c.setCellValue(saldo.doubleValue());
						c.setCellStyle(csValue);
						row++;
			}
			//footer
			r = getRow(sheet, row);
			c = getCell(r, 0);
			c.setCellValue("Итого");
			c.setCellStyle(csNameB);

			Calendar ca = Calendar.getInstance();
			ca.setTime(mperiod.getStartDate());
			
			BigDecimal summm = Env.ZERO;
			
			int count = BanksName.size();
			for(int d = 0; d <= Days+1; d++){
				double footersum = .0;
				for(int e = 1; e <= count; e++){
					Row rr = sheet.getRow((row-e));
					Cell ce = rr.getCell(d+1);
					try{
						double b = ce.getNumericCellValue();
						footersum += b;
					}catch(Exception ee){}
					
				}
				c = getCell(r, d+1);
				if(footersum != 0)
					c.setCellValue(footersum);
				c.setCellStyle(csValue);
				summm = summm.add(new BigDecimal(footersum));
				sheet.autoSizeColumn(d+1);
			}
			
		}
		
		
		//--------------------------------------------- 2 -----------------		
		// bank curr cash
		List<line> listBankCurrCash = getBanksCurrCash1(mperiod);
		if(listBankCurrCash.size() > 0){
			row++;

			//caption
			r = getRow(sheet, row);
			c = getCell(r, 0);
			c.setCellValue("Денежные средства на текущих счетах всего:");
			setRowStyle(sheet, Days, r, csNameB);
			c.setCellStyle(csNameB);
			
			//data
			row++;

			List<String> BanksName = new ArrayList<String>();
			for(int i=0; i < listBankCurrCash.size(); i++){
				if(BanksName.indexOf(listBankCurrCash.get(i).name) == -1)
					BanksName.add(listBankCurrCash.get(i).name);
			}
			
			
			for(int j=0; j < BanksName.size(); j++){
						r = getRow(sheet, row);
						c = getCell(r, 0);
						c.setCellValue("  "+BanksName.get(j));
						c.setCellStyle(csName);
						
						Calendar ca = Calendar.getInstance();
						ca.setTime(mperiod.getStartDate());
						ca.add(ca.DATE, -1);
						BigDecimal summ = Env.ZERO;
						
						int c_bank_id = 0;
						for(int i=0; i < listBankDeposit.size(); i++){
							if(BanksName.get(j).equals(listBankCurrCash.get(i).name))
								c_bank_id = listBankCurrCash.get(i).c_bank_id;
						}
						
						BigDecimal saldo = getBanksCurrCashSaldo1(mperiod, c_bank_id); 

						if(saldo.compareTo(Env.ZERO) == 0)
							continue;
						
						for(int d = 1; d <= Days+1; d++){
							for(int i=0; i < listBankCurrCash.size(); i++){
								if(BanksName.get(j).equals(listBankCurrCash.get(i).name) && listBankCurrCash.get(i).dateacct.equals(ca.getTime())){
										c = getCell(r, d);
										if(d ==1) continue;
										saldo = saldo.add(listBankCurrCash.get(i).sum);
										c.setCellValue(saldo.doubleValue());
										c.setCellStyle(csValue);
										summ = summ.add(saldo);
								}else{
									c = getCell(r, d);
									c.setCellValue(saldo.doubleValue());
									c.setCellStyle(csValue);	
								}
							}
							ca.add(Calendar.DATE, 1);
						}
						
						c = getCell(r, Days+2);
						c.setCellValue(saldo.doubleValue());
						c.setCellStyle(csValue);
						
						row++;
			}
			//footer
			r = getRow(sheet, row);
			c = getCell(r, 0);
			c.setCellValue("Итого");
			c.setCellStyle(csNameB);

			Calendar ca = Calendar.getInstance();
			ca.setTime(mperiod.getStartDate());
			
//			BigDecimal summm = Env.ZERO;
			int count = BanksName.size();
			for(int d = 0; d <= Days+1; d++){
				double footersum = .0;
				for(int e = 1; e <= count; e++){
					Row rr = sheet.getRow((row-e));
					Cell ce = rr.getCell(d+1);
					try{
						double b = ce.getNumericCellValue();
						footersum += b;
					}catch(Exception ee){}
					
				}
				c = getCell(r, d+1);
				if(footersum != 0)
					c.setCellValue(footersum);
				c.setCellStyle(csValue);
			}

		}		
		
        //payemnts
        //Движение денег от операционной деятельности

    	row++;

        //caption
        r = getRow(sheet, row);
        c = getCell(r, 0);
        c.setCellValue("Движение денег от операционной деятельности");
        c.setCellStyle(csNameB);
        setRowStyle(sheet, Days, r, csNameB);

        List<line> listPayments = getPayemnts(mperiod, "011,012,013,014,015,016");
        List<line> listPayments2 = getPayemnts(mperiod, "021,022,023,024,025,026,027");
        if(listPayments.size() == 0 && listPayments2.size() == 0)
        	row--;
		

		
		//--------------------------------------------- 3 -----------------
        listPayments = getPayemnts(mperiod, "011,012,013,014,015,016");
        if(listPayments.size() > 0){
        	
            
            row++;        	
        	
            //caption
            r = getRow(sheet, row);
            c = getCell(r, 0);
            c.setCellValue("	Поступление денег");
            c.setCellStyle(csNameB);
            setRowStyle(sheet, Days, r, csNameB);
            
            //data
            row++;
            
            Row1 = row;

            List<String> BanksName = new ArrayList<String>();
            for(int i=0; i < listPayments.size(); i++){
                if(BanksName.indexOf(listPayments.get(i).name) == -1)
                    BanksName.add(listPayments.get(i).name);
            }
            
            
            for(int j=0; j < BanksName.size(); j++){
                        r = getRow(sheet, row);
                        c = getCell(r, 0);
                        c.setCellValue("  "+BanksName.get(j));
                        c.setCellStyle(csName);
                        
                        Calendar ca = Calendar.getInstance();
                        ca.setTime(mperiod.getStartDate());
                        BigDecimal summ = Env.ZERO;
                        
                        for(int d = 1; d <= Days+1; d++){
                            for(int i=0; i < listPayments.size(); i++){
                                if(BanksName.get(j).equals(listPayments.get(i).name) && listPayments.get(i).dateacct.equals(ca.getTime())){
                                        c = getCell(r, d);
                                        c.setCellValue(listPayments.get(i).sum.doubleValue());
                                        c.setCellStyle(csValue);
                                        summ = summ.add(listPayments.get(i).sum);
                                }else{
                                    c = getCell(r, d);
//                                    c.setCellValue(Env.ZERO.doubleValue());
                                    c.setCellStyle(csValue);
                                }
                            }
                            ca.add(Calendar.DATE, 1);
                        }
                        
                        c = getCell(r, Days+2);
                        c.setCellValue(summ.doubleValue());
                        c.setCellStyle(csValue);                        
                        row++;
            }
            
            Row2 = row;
            row--;
//            //footer
//            r = getRow(sheet, row);
//            c = getCell(r, 0);
//            c.setCellValue("Итого");
//            c.setCellStyle(csNameB);
//
//            Calendar ca = Calendar.getInstance();
//            ca.setTime(mperiod.getStartDate());
//            
//            BigDecimal summm = Env.ZERO;
//            for(int d = 0; d <= Days; d++){
//                BigDecimal summ = Env.ZERO;
//                
//                for(int i=0; i < listPayments.size(); i++){
//                    if(listPayments.get(i).dateacct.equals(ca.getTime())){
//                            c = getCell(r, d+1);
//                            c.setCellValue(listPayments.get(i).sum.doubleValue());
//                            c.setCellStyle(csValue);
//                            summ = summ.add(listPayments.get(i).sum);
//                    }
//                }
//                c = getCell(r, d+1);
//                if(summ.compareTo(Env.ZERO) == 1)
//                c.setCellValue(summ.doubleValue());
//                c.setCellStyle(csValue);
//                summm = summm.add(summ);
//                ca.add(Calendar.DATE, 1);
//            }
//            c = getCell(r, Days+2);
//            c.setCellValue(summm.doubleValue());
//			c.setCellStyle(csValue);
            
            
        }        
        
		//--------------------------------------------- 4 -----------------
        //    Выбытие денег
        listPayments = getPayemnts(mperiod, "021,022,023,024,025,026,027");
        if(listPayments.size() > 0){
        	
        	row++;

            //caption
            r = getRow(sheet, row);
            c = getCell(r, 0);
            c.setCellValue("	Выбытие денег");
            c.setCellStyle(csNameB);
            setRowStyle(sheet, Days, r, csNameB);
            
            //data
            row++;

            List<String> BanksName = new ArrayList<String>();
            for(int i=0; i < listPayments.size(); i++){
                if(BanksName.indexOf(listPayments.get(i).name) == -1)
                    BanksName.add(listPayments.get(i).name);
            }
            
            
            for(int j=0; j < BanksName.size(); j++){
                        r = getRow(sheet, row);
                        c = getCell(r, 0);
                        c.setCellValue("  "+BanksName.get(j));
                        c.setCellStyle(csName);

                        
                        Calendar ca = Calendar.getInstance();
                        ca.setTime(mperiod.getStartDate());
                        BigDecimal summ = Env.ZERO;
                        
                        for(int d = 1; d <= Days+1; d++){
                            for(int i=0; i < listPayments.size(); i++){
                                if(BanksName.get(j).equals(listPayments.get(i).name) && listPayments.get(i).dateacct.equals(ca.getTime())){
                                        c = getCell(r, d);
                                        c.setCellValue(listPayments.get(i).sum.doubleValue());
                                        c.setCellStyle(csValue);
                                        summ = summ.add(listPayments.get(i).sum);
                                }else{
                                    c = getCell(r, d);
//                                    c.setCellValue(Env.ZERO.doubleValue());
                                    c.setCellStyle(csValue);
                                }
                            }
                            ca.add(Calendar.DATE, 1);
                        }
                        
                        c = getCell(r, Days+2);
                        c.setCellValue(summ.doubleValue());
                        c.setCellStyle(csValue);                        
                        row++;
            }
            //footer
            r = getRow(sheet, row);
            c = getCell(r, 0);
            c.setCellValue("Увеличение/уменьшение денег от операционной деятельности");
            c.setCellStyle(csNameB);
//
//            Calendar ca = Calendar.getInstance();
//            ca.setTime(mperiod.getStartDate());
//            
//            BigDecimal summm = Env.ZERO;
//            for(int d = 0; d <= Days; d++){
//                BigDecimal summ = Env.ZERO;
//                
//                for(int i=0; i < listPayments.size(); i++){
//                    if(listPayments.get(i).dateacct.equals(ca.getTime())){
//                            c = getCell(r, d+1);
//                            c.setCellValue(listPayments.get(i).sum.doubleValue());
//                            c.setCellStyle(csValue);
//                            summ = summ.add(listPayments.get(i).sum);
//                    }
//                }
//                c = getCell(r, d+1);
//                if(summ.compareTo(Env.ZERO) == 1)
//                c.setCellValue(summ.doubleValue());
//                c.setCellStyle(csValue);
//                summm = summm.add(summ);
//                ca.add(Calendar.DATE, 1);
//            }
//            c = getCell(r, Days+2);
//            c.setCellValue(summm.doubleValue());
//            c.setCellStyle(csValue);
            
			int count = row - Row1;
			
			for(int d = 0; d <= Days+1; d++){
				double footersum = .0;
				for(int e = 1; e <= count; e++){
					Row rr = getRow(sheet, (row-e));
					Cell ce = getCell(rr,d+1);
					try{
						double b = ce.getNumericCellValue();
						if(Row2 < (row-e))
							footersum -= b;
						else
							footersum += b;
					}catch(Exception ee){}
					
				}
				c = getCell(r, d+1);
				if(footersum != 0)
					c.setCellValue(footersum);
				c.setCellStyle(csValue);
			}
            
            
        }        
        
        row++;

        
        	
        //caption
        r = getRow(sheet, row);
        c = getCell(r, 0);
        c.setCellValue("Движение денег от инвестиционной деятельности");
        c.setCellStyle(csNameB);
        setRowStyle(sheet, Days, r, csNameB);
        
        listPayments = getPayemnts(mperiod, "041,042,043,044,045,046,047,048,049,050,051");
        listPayments2 = getPayemnts(mperiod, "061,062,063,064,065,066,067,068,069,070,071");
        if(listPayments.size() == 0 && listPayments2.size() == 0)
        	row--;
        
        
        Row1 = Row2 = 0;
		//--------------------------------------------- 5 -----------------
        //Увеличение/уменьшение денег от инвестиционной деятельности 

        listPayments = getPayemnts(mperiod, "041,042,043,044,045,046,047,048,049,050,051");
        if(listPayments.size() > 0){
            
            //    
            row++;

            //caption
            r = getRow(sheet, row);
            c = getCell(r, 0);
            c.setCellValue("	Поступление денег ");
            setRowStyle(sheet, Days, r, csNameB);
            c.setCellStyle(csNameB);
            
            //data
            row++;


            List<String> BanksName = new ArrayList<String>();
            for(int i=0; i < listPayments.size(); i++){
                if(BanksName.indexOf(listPayments.get(i).name) == -1)
                    BanksName.add(listPayments.get(i).name);
            }
            
            
            for(int j=0; j < BanksName.size(); j++){
                        r = getRow(sheet, row);
                        c = getCell(r, 0);
                        c.setCellValue("  "+BanksName.get(j));
                        c.setCellStyle(csName);
                        
                        Calendar ca = Calendar.getInstance();
                        ca.setTime(mperiod.getStartDate());
                        BigDecimal summ = Env.ZERO;
                        
                        for(int d = 1; d <= Days; d++){
                            for(int i=0; i < listPayments.size(); i++){
                                if(BanksName.get(j).equals(listPayments.get(i).name) && listPayments.get(i).dateacct.equals(ca.getTime())){
                                        c = getCell(r, d);
                                        c.setCellValue(listPayments.get(i).sum.doubleValue());
                                        c.setCellStyle(csValue);
                                        summ = summ.add(listPayments.get(i).sum);
                                }else{
                                    c = getCell(r, d);
//                                    c.setCellValue(Env.ZERO.doubleValue());
                                    c.setCellStyle(csValue);
                                }
                            }
                            ca.add(Calendar.DATE, 1);
                        }
                        
                        c = getCell(r, Days+2);
                        c.setCellValue(summ.doubleValue());
                        c.setCellStyle(csValue);
                        row++;
            }
            //footer
            Row1 = row-1;
            row--;
//            r = getRow(sheet, row);
//            c = getCell(r, 0);
//            c.setCellValue("Итого");
//            c.setCellStyle(csNameB);
//
//            Calendar ca = Calendar.getInstance();
//            ca.setTime(mperiod.getStartDate());
//            
//            BigDecimal summm = Env.ZERO;
//            for(int d = 0; d <= Days; d++){
//                BigDecimal summ = Env.ZERO;
//                
//                for(int i=0; i < listPayments.size(); i++){
//                    if(listPayments.get(i).dateacct.equals(ca.getTime())){
//                            c = getCell(r, d+1);
//                            c.setCellValue(listPayments.get(i).sum.doubleValue());
//                            c.setCellStyle(csValue);
//                            summ = summ.add(listPayments.get(i).sum);
//                    }
//                }
//                c = getCell(r, d+1);
//                if(summ.compareTo(Env.ZERO) == 1)
//                c.setCellValue(summ.doubleValue());
//                c.setCellStyle(csValue);
//                summm = summm.add(summ);
//                ca.add(Calendar.DATE, 1);
//            }
//            c = getCell(r, Days+2);
//            c.setCellValue(summm.doubleValue());
//            c.setCellStyle(csValue);
            
        }    



		//--------------------------------------------- 6 -----------------    
        
        listPayments = getPayemnts(mperiod, "061,062,063,064,065,066,067,068,069,070,071");
        if(listPayments.size() > 0){
        	
        	row++;
        	
            //caption
            r = getRow(sheet, row);
            c = getCell(r, 0);
            c.setCellValue("	Выбытие денег ");
            setRowStyle(sheet, Days, r, csNameB);
            c.setCellStyle(csNameB);            
            
            //data
            row++;

            Row2 = row;

            List<String> BanksName = new ArrayList<String>();
            for(int i=0; i < listPayments.size(); i++){
                if(BanksName.indexOf(listPayments.get(i).name) == -1)
                    BanksName.add(listPayments.get(i).name);
            }
            
            
            for(int j=0; j < BanksName.size(); j++){
                        r = getRow(sheet, row);
                        c = getCell(r, 0);
                        c.setCellValue("  "+BanksName.get(j));
                        c.setCellStyle(csName);
                        setRowStyle(sheet, Days, r, csName);
                        
                        Calendar ca = Calendar.getInstance();
                        ca.setTime(mperiod.getStartDate());
                        BigDecimal summ = Env.ZERO;
                        
                        for(int d = 1; d <= Days; d++){
                            for(int i=0; i < listPayments.size(); i++){
                                if(BanksName.get(j).equals(listPayments.get(i).name) && listPayments.get(i).dateacct.equals(ca.getTime())){
                                        c = getCell(r, d);
                                        c.setCellValue(listPayments.get(i).sum.doubleValue());
                                        c.setCellStyle(csValue);
                                        summ = summ.add(listPayments.get(i).sum);
                                }else{
                                    c = getCell(r, d);
//                                    c.setCellValue(Env.ZERO.doubleValue());
                                    c.setCellStyle(csValue);
                                }
                            }
                            ca.add(Calendar.DATE, 1);
                        }
                        
                        c = getCell(r, Days+2);
                        c.setCellValue(summ.doubleValue());
                        c.setCellStyle(csValue);
                        
                        row++;
            }
            
            


//			row--;
			//footer
//			r = getRow(sheet, row);
//			c = getCell(r, 0);
//			c.setCellValue("Итого");
//			c.setCellStyle(csNameB);			
//
//			Calendar ca = Calendar.getInstance();
//			ca.setTime(mperiod.getStartDate());
//			
//			BigDecimal summm = Env.ZERO;
//			
//			int count = BanksName.size();
//			for(int d = 0; d <= Days+1; d++){
//				double footersum = .0;
//				for(int e = 1; e <= count; e++){
//					Row rr = sheet.getRow((row-e));
//					Cell ce = rr.getCell(d+1);
//					try{
//						double b = ce.getNumericCellValue();
//						footersum += b;
//					}catch(Exception ee){}
//					
//				}
//				c = getCell(r, d+1);
//				c.setCellValue(footersum);
//				summm = summm.add(new BigDecimal(footersum));
//				c.setCellStyle(csValue);
//			}
			
			
			
            //footer
            r = getRow(sheet, row);
            c = getCell(r, 0);
            c.setCellValue("Увеличение/уменьшение денег от инвестиционной деятельности");
            c.setCellStyle(csNameB);

            if(Row1 > 0 && Row2 > 0){
    			int count = row - Row1;
    			
    			for(int d = 0; d <= Days+1; d++){
    				double footersum = .0;
    				for(int e = 1; e <= count; e++){
    					Row rr = getRow(sheet, (row-e));
    					Cell ce = getCell(rr,d+1);
    					try{
    						double b = ce.getNumericCellValue();
    						if(Row2 <= (row-e))
    							footersum -= b;
    						else
    							footersum += b;
    					}catch(Exception ee){}
    					
    				}
    				c = getCell(r, d+1);
    				if(footersum != 0)
    					c.setCellValue(footersum);
    				c.setCellStyle(csValue);
    			}
                

            }else{
	            Calendar ca = Calendar.getInstance();
	            ca.setTime(mperiod.getStartDate());
	            
	            BigDecimal summm = Env.ZERO;
	            for(int d = 0; d <= Days; d++){
	                BigDecimal summ = Env.ZERO;
	                
	                for(int i=0; i < listPayments.size(); i++){
	                    if(listPayments.get(i).dateacct.equals(ca.getTime())){
	                            c = getCell(r, d+1);
	                            c.setCellValue(listPayments.get(i).sum.doubleValue());
	                            c.setCellStyle(csValue);
	                            summ = summ.add(listPayments.get(i).sum);
	                    }
	                }
	                c = getCell(r, d+1);
	                if(summ.compareTo(Env.ZERO) == 1)
	                c.setCellValue(summ.doubleValue());
	                c.setCellStyle(csValue);
	                summm = summm.add(summ);
	                ca.add(Calendar.DATE, 1);
	            }
	            c = getCell(r, Days+2);
	            c.setCellValue(summm.doubleValue());
	            c.setCellStyle(csValue);            
            }
			
			
		  
		}      
        
        
        // deposits ------------------------------666
        
        row++;
        
        Row1 = Row2 = 0;
        
        r = getRow(sheet, row);
        c = getCell(r, 0);
        c.setCellValue("Депозиты");
        setRowStyle(sheet, Days, r, csNameB);
        c.setCellStyle(csNameB);            

        if(getBanksDeposit4(mperiod).size() == 0 && getBanksDeposit3(mperiod).size() == 0)
        	row--;
        
		//--------------------------------------------- 6.2 -----------------
		// bank deposit
//        row++;
        
		listBankDeposit = getBanksDeposit3(mperiod);
		if(listBankDeposit.size() > 0){
			row++;
			
			//caption
			r = getRow(sheet,row);
			c = getCell(r, 0);
			c.setCellValue("	Размещение депозитов:");
			c.setCellStyle(csNameB);			
			setRowStyle(sheet, Days, r, csNameB);
			
			//data
			row++;
			Row1 = row;

			List<String>BanksName = new ArrayList<String>();
			for(int i=0; i < listBankDeposit.size(); i++){
				if(BanksName.indexOf(listBankDeposit.get(i).name) == -1)
					BanksName.add(listBankDeposit.get(i).name);
			}
			
			
			for(int j=0; j < BanksName.size(); j++){
						r = getRow(sheet, row);
						c = getCell(r, 0);
						c.setCellValue("  "+BanksName.get(j));
						c.setCellStyle(csName);

						
						Calendar ca = Calendar.getInstance();
						ca.setTime(mperiod.getStartDate());
						BigDecimal summ = Env.ZERO;
						BigDecimal cash = Env.ZERO;
						
						int c_bank_id = 0;
						for(int i=0; i < listBankDeposit.size(); i++){
							if(BanksName.get(j).equals(listBankDeposit.get(i).name))
								c_bank_id = listBankDeposit.get(i).c_bank_id;
						}
						
						BigDecimal saldo = Env.ZERO;// getBanksDepositSaldo2(mperiod, c_bank_id); 
						
//						if(saldo.compareTo(Env.ZERO) == 0)
//							continue;

						
						for(int d = 1; d <= Days+1; d++){
							for(int i=0; i < listBankDeposit.size(); i++){
								if(BanksName.get(j).equals(listBankDeposit.get(i).name) && listBankDeposit.get(i).dateacct.equals(ca.getTime())){
										c = getCell(r, d);
										if(listBankDeposit.get(i).sum.doubleValue() != 0)
										c.setCellValue(listBankDeposit.get(i).sum.doubleValue());
										saldo = saldo.add(listBankDeposit.get(i).sum);
//										c.setCellValue(saldo.doubleValue());
										c.setCellStyle(csValue);
										summ = summ.add(saldo);
//										cash = saldo;//listBankDeposit.get(i).sum;
								}else{
									c = getCell(r, d);
//									c.setCellValue(saldo.doubleValue());
									c.setCellStyle(csValue);
								}
							}
							ca.add(Calendar.DATE, 1);
						}
						
						c = getCell(r, Days+2);
						c.setCellValue(saldo.doubleValue());
						c.setCellStyle(csValue);
						row++;
			}
			row++;
			//footer
//			r = getRow(sheet, row);
//			c = getCell(r, 0);
//			c.setCellValue("Итого");
//			c.setCellStyle(csNameB);			
//
//			Calendar ca = Calendar.getInstance();
//			ca.setTime(mperiod.getStartDate());
//			
//			BigDecimal summm = Env.ZERO;
//			
//			int count = BanksName.size();
//			for(int d = 0; d <= Days+1; d++){
//				double footersum = .0;
//				for(int e = 1; e <= count; e++){
//					Row rr = sheet.getRow((row-e));
//					Cell ce = rr.getCell(d+1);
//					try{
//						double b = ce.getNumericCellValue();
//						footersum += b;
//					}catch(Exception ee){}
//					
//				}
//				c = getCell(r, d+1);
//				c.setCellValue(footersum);
//				summm = summm.add(new BigDecimal(footersum));
//				c.setCellStyle(csValue);
//			}
			
			
			
			
			
		}  
		
		//--------------------------------------------- 6.2 -----------------
		// bank deposit
		listBankDeposit = getBanksDeposit4(mperiod);
		if(listBankDeposit.size() > 0){
			row--;
			
			//caption
			r = getRow(sheet,row);
			c = getCell(r, 0);
			c.setCellValue("	Изьятие с депозита:");
			c.setCellStyle(csNameB);			
			setRowStyle(sheet, Days, r, csNameB);
			
			//data
			row++;

			Row2 = row;
			
			List<String> BanksName = new ArrayList<String>();
			for(int i=0; i < listBankDeposit.size(); i++){
				if(BanksName.indexOf(listBankDeposit.get(i).name) == -1)
					BanksName.add(listBankDeposit.get(i).name);
			}
			
			
			for(int j=0; j < BanksName.size(); j++){
						r = getRow(sheet, row);
						c = getCell(r, 0);
						c.setCellValue("  "+BanksName.get(j));
						c.setCellStyle(csName);

						
						Calendar ca = Calendar.getInstance();
						ca.setTime(mperiod.getStartDate());
						BigDecimal summ = Env.ZERO;
						BigDecimal cash = Env.ZERO;
						
						int c_bank_id = 0;
						for(int i=0; i < listBankDeposit.size(); i++){
							if(BanksName.get(j).equals(listBankDeposit.get(i).name))
								c_bank_id = listBankDeposit.get(i).c_bank_id;
						}
						
						BigDecimal saldo = Env.ZERO;// getBanksDepositSaldo2(mperiod, c_bank_id); 
						
//						if(saldo.compareTo(Env.ZERO) == 0)
//							continue;

						
						for(int d = 1; d <= Days+1; d++){
							for(int i=0; i < listBankDeposit.size(); i++){
								if(BanksName.get(j).equals(listBankDeposit.get(i).name) && listBankDeposit.get(i).dateacct.equals(ca.getTime())){
										c = getCell(r, d);
										if(listBankDeposit.get(i).sum.doubleValue() != 0)
										c.setCellValue(listBankDeposit.get(i).sum.doubleValue());
										saldo = saldo.add(listBankDeposit.get(i).sum);
//										c.setCellValue(saldo.doubleValue());
										c.setCellStyle(csValue);
										summ = summ.add(saldo);
//										cash = saldo;//listBankDeposit.get(i).sum;
								}else{
									c = getCell(r, d);
//									c.setCellValue(summ.doubleValue());
									c.setCellStyle(csValue);
								}
							}
							ca.add(Calendar.DATE, 1);
						}
						
						c = getCell(r, Days+2);
						c.setCellValue(saldo.doubleValue());
						c.setCellStyle(csValue);
						row++;
			}
			row--;
			//footer
//			r = getRow(sheet, row);
//			c = getCell(r, 0);
//			c.setCellValue("Итого");
//			c.setCellStyle(csNameB);			
//
//			Calendar ca = Calendar.getInstance();
//			ca.setTime(mperiod.getStartDate());
//			
//			BigDecimal summm = Env.ZERO;
//			
//			int count = BanksName.size();
//			for(int d = 0; d <= Days+1; d++){
//				double footersum = .0;
//				for(int e = 1; e <= count; e++){
//					Row rr = sheet.getRow((row-e));
//					Cell ce = rr.getCell(d+1);
//					try{
//						double b = ce.getNumericCellValue();
//						footersum += b;
//					}catch(Exception ee){}
//					
//				}
//				c = getCell(r, d+1);
//				c.setCellValue(footersum);
//				summm = summm.add(new BigDecimal(footersum));
//				c.setCellStyle(csValue);
//			}
			
            //footer
//            r = getRow(sheet, row);
//            c = getCell(r, 0);
//            c.setCellValue("Денежные средства на депозитных счетах всего:");
//            c.setCellStyle(csNameB);
//
//            if(Row1 > 0 && Row2 > 0){
//    			int count = row - Row1;
//    			
//    			for(int d = 0; d <= Days+1; d++){
//    				double footersum = .0;
//    				for(int e = 1; e <= count; e++){
//    					Row rr = getRow(sheet, (row-e));
//    					Cell ce = getCell(rr,d+1);
//    					try{
//    						double b = ce.getNumericCellValue();
//    						if(Row2 < (row-e))
//    							footersum -= b;
//    						else
//    							footersum += b;
//    					}catch(Exception ee){}
//    					
//    				}
//    				c = getCell(r, d+1);
//    				if(footersum != 0)
//    					c.setCellValue(footersum);
//    				c.setCellStyle(csValue);
//    			}
//                
//
//            }else{
//	            Calendar ca = Calendar.getInstance();
//	            ca.setTime(mperiod.getStartDate());
//	            
//	            BigDecimal summm = Env.ZERO;
//	            for(int d = 0; d <= Days; d++){
//	                BigDecimal summ = Env.ZERO;
//	                
//	                for(int i=0; i < listPayments.size(); i++){
//	                    if(listPayments.get(i).dateacct.equals(ca.getTime())){
//	                            c = getCell(r, d+1);
//	                            c.setCellValue(listPayments.get(i).sum.doubleValue());
//	                            c.setCellStyle(csValue);
//	                            summ = summ.add(listPayments.get(i).sum);
//	                    }
//	                }
//	                c = getCell(r, d+1);
//	                if(summ.compareTo(Env.ZERO) == 1)
//	                c.setCellValue(summ.doubleValue());
//	                c.setCellStyle(csValue);
//	                summm = summm.add(summ);
//	                ca.add(Calendar.DATE, 1);
//	            }
//	            c = getCell(r, Days+2);
//	            c.setCellValue(summm.doubleValue());
//	            c.setCellStyle(csValue);            
//            }
//			
			
		}
        
        
        
        
        
        
        
        
        
        
        
        
        
        // end of deposits ------------------------------666        
        
        Row1 = Row2 = 0;
		//--------------------------------------------- 7 -----------------
        //Увеличение/уменьшение денег от финансовой  деятельности 
        row++;

        //caption
        r = getRow(sheet, row);
        c = getCell(r, 0);
        c.setCellValue("Движение денег от финансовой деятельности");
        setRowStyle(sheet, Days, r, csNameB);
        c.setCellStyle(csNameB);            

        listPayments = getPayemnts(mperiod, "091,092,093,094");
        listPayments2 = getPayemnts(mperiod, "101,102,103,104,105");
        if(listPayments.size() == 0 && listPayments2.size() == 0)
        	row--;
        
        
        listPayments = getPayemnts(mperiod, "091,092,093,094");
        if(listPayments.size() > 0){

            //    
            row++;
        	

            //caption
            r = getRow(sheet, row);
            c = getCell(r, 0);
            c.setCellValue("	Поступление денег ");
            setRowStyle(sheet, Days, r, csNameB);
            c.setCellStyle(csNameB);
            
            //data
            row++;


            List<String> BanksName = new ArrayList<String>();
            for(int i=0; i < listPayments.size(); i++){
                if(BanksName.indexOf(listPayments.get(i).name) == -1)
                    BanksName.add(listPayments.get(i).name);
            }
            
            
            for(int j=0; j < BanksName.size(); j++){
                        r = getRow(sheet, row);
                        c = getCell(r, 0);
                        c.setCellValue("  "+BanksName.get(j));
                        c.setCellStyle(csValue);
                        
                        
                        Calendar ca = Calendar.getInstance();
                        ca.setTime(mperiod.getStartDate());
                        BigDecimal summ = Env.ZERO;
                        
                        for(int d = 1; d <= Days; d++){
                            for(int i=0; i < listPayments.size(); i++){
                                if(BanksName.get(j).equals(listPayments.get(i).name) && listPayments.get(i).dateacct.equals(ca.getTime())){
                                        c = getCell(r, d);
                                        c.setCellValue(listPayments.get(i).sum.doubleValue());
                                        c.setCellStyle(csValue);
                                        summ = summ.add(listPayments.get(i).sum);
                                }else{
                                    c = getCell(r, d);
//                                    c.setCellValue(Env.ZERO.doubleValue());
                                    c.setCellStyle(csValue);
                                }
                            }
                            ca.add(Calendar.DATE, 1);
                        }
                        
                        c = getCell(r, Days+2);
                        c.setCellValue(summ.doubleValue());
                        c.setCellStyle(csValue);                        
                        row++;
            }
            //footer
            Row1 = row;
            row--;
//            r = getRow(sheet, row);
//            c = getCell(r, 0);
//            c.setCellValue("Итого");
//            c.setCellStyle(csNameB);
//
//            Calendar ca = Calendar.getInstance();
//            ca.setTime(mperiod.getStartDate());
//            
//            BigDecimal summm = Env.ZERO;
//            for(int d = 0; d <= Days; d++){
//                BigDecimal summ = Env.ZERO;
//                
//                for(int i=0; i < listPayments.size(); i++){
//                    if(listPayments.get(i).dateacct.equals(ca.getTime())){
//                            c = getCell(r, d+1);
//                            c.setCellValue(listPayments.get(i).sum.doubleValue());
//                            c.setCellStyle(csValue);
//                            summ = summ.add(listPayments.get(i).sum);
//                    }
//                }
//                c = getCell(r, d+1);
//                if(summ.compareTo(Env.ZERO) == 1)
//                c.setCellValue(summ.doubleValue());
//                c.setCellStyle(csValue);
//                summm = summm.add(summ);
//                ca.add(Calendar.DATE, 1);
//            }
//            c = getCell(r, Days+2);
//            c.setCellValue(summm.doubleValue());
//            c.setCellStyle(csValue);            
            
            
        }    
        
		//--------------------------------------------- 8 -----------------
        listPayments = getPayemnts(mperiod, "101,102,103,104,105");
        if(listPayments.size() > 0){

            row++;

            //caption
            r = getRow(sheet, row);
            c = getCell(r, 0);
            c.setCellValue("Выбытие денег1 ");
            setRowStyle(sheet, Days, r, csNameB);
            c.setCellStyle(csNameB);            
            //data
            row++;

            Row2 = row;
            
            List<String> BanksName = new ArrayList<String>();
            for(int i=0; i < listPayments.size(); i++){
                if(BanksName.indexOf(listPayments.get(i).name) == -1)
                    BanksName.add(listPayments.get(i).name);
            }
            
            
            for(int j=0; j < BanksName.size(); j++){
                        r = getRow(sheet, row);
                        c = getCell(r, 0);
                        c.setCellValue("  "+BanksName.get(j));
                        c.setCellStyle(csName);
                        
                        
                        Calendar ca = Calendar.getInstance();
                        ca.setTime(mperiod.getStartDate());
                        BigDecimal summ = Env.ZERO;
                        
                        for(int d = 1; d <= Days; d++){
                            for(int i=0; i < listPayments.size(); i++){
                                if(BanksName.get(j).equals(listPayments.get(i).name) && listPayments.get(i).dateacct.equals(ca.getTime())){
                                        c = getCell(r, d);
                                        c.setCellValue(listPayments.get(i).sum.doubleValue());
                                        c.setCellStyle(csValue);
                                        summ = summ.add(listPayments.get(i).sum);
                                }else{
                                    c = getCell(r, d);
//                                    c.setCellValue(Env.ZERO.doubleValue());
                                    c.setCellStyle(csValue);
                                }
                            }
                            ca.add(Calendar.DATE, 1);
                        }
                        
                        c = getCell(r, Days+2);
                        c.setCellValue(summ.doubleValue());
                        c.setCellStyle(csValue);
                        row++;
            }
            //footer
            
            if(Row1 > 0 && Row2 > 0){
    			int count = row - Row1;
    			
    			for(int d = 0; d <= Days+1; d++){
    				double footersum = .0;
    				for(int e = 1; e <= count; e++){
    					Row rr = getRow(sheet, (row-e));
    					Cell ce = getCell(rr,d+1);
    					try{
    						double b = ce.getNumericCellValue();
    						if(Row2 < (row-e))
    							footersum -= b;
    						else
    							footersum += b;
    					}catch(Exception ee){}
    					
    				}
    				c = getCell(r, d+1);
    				if(footersum != 0)
    					c.setCellValue(footersum);
    				c.setCellStyle(csValue);
    			}
                

            }else{            
	            r = getRow(sheet, row);
	            c = getCell(r, 0);
	            c.setCellValue("Увеличение/уменьшение денег от финансовой деятельности");
	            c.setCellStyle(csNameB);
	
	            Calendar ca = Calendar.getInstance();
	            ca.setTime(mperiod.getStartDate());
	            
	            BigDecimal summm = Env.ZERO;
	            for(int d = 0; d <= Days; d++){
	                BigDecimal summ = Env.ZERO;
	                
	                for(int i=0; i < listPayments.size(); i++){
	                    if(listPayments.get(i).dateacct.equals(ca.getTime())){
	                            c = getCell(r, d+1);
	                            c.setCellValue(listPayments.get(i).sum.doubleValue());
	                            c.setCellStyle(csValue);
	                            summ = summ.add(listPayments.get(i).sum);
	                    }
	                }
	                c = getCell(r, d+1);
	                if(summ.compareTo(Env.ZERO) == 1)
	                c.setCellValue(summ.doubleValue());
	                c.setCellStyle(csValue);
	                summm = summm.add(summ);
	                ca.add(Calendar.DATE, 1);
	            }
	            c = getCell(r, Days+2);
	            c.setCellValue(summm.doubleValue());
	            c.setCellStyle(csValue);            
            }
            
        }    
        		
		//--------------------------------------------- 9 -----------------
		// bank deposit
		listBankDeposit = getBanksDeposit2(mperiod);
		if(listBankDeposit.size() > 0){
			row++;
			
			//caption
			r = getRow(sheet,row);
			c = getCell(r, 0);
			c.setCellValue("Денежные средства на депозитных счетах всего:");
			c.setCellStyle(csNameB);			
			setRowStyle(sheet, Days, r, csNameB);
			
			//data
			row++;


			List<String> BanksName = new ArrayList<String>();
			for(int i=0; i < listBankDeposit.size(); i++){
				if(BanksName.indexOf(listBankDeposit.get(i).name) == -1)
					BanksName.add(listBankDeposit.get(i).name);
			}
			
			
			for(int j=0; j < BanksName.size(); j++){
						r = getRow(sheet, row);
						c = getCell(r, 0);
						c.setCellValue("  "+BanksName.get(j));
						c.setCellStyle(csName);

						
						Calendar ca = Calendar.getInstance();
						ca.setTime(mperiod.getStartDate());
						BigDecimal summ = Env.ZERO;
						BigDecimal cash = Env.ZERO;
						
						int c_bank_id = 0;
						for(int i=0; i < listBankDeposit.size(); i++){
							if(BanksName.get(j).equals(listBankDeposit.get(i).name))
								c_bank_id = listBankDeposit.get(i).c_bank_id;
						}
						
						BigDecimal saldo = getBanksDepositSaldo2(mperiod, c_bank_id); 
						
						if(saldo.compareTo(Env.ZERO) == 0)
							continue;

						
						for(int d = 1; d <= Days+1; d++){
							for(int i=0; i < listBankDeposit.size(); i++){
								if(BanksName.get(j).equals(listBankDeposit.get(i).name) && listBankDeposit.get(i).dateacct.equals(ca.getTime())){
										c = getCell(r, d);
										saldo = saldo.add(listBankDeposit.get(i).sum);
										c.setCellValue(saldo.doubleValue());
										c.setCellStyle(csValue);
										summ = summ.add(saldo);
										cash = saldo;//listBankDeposit.get(i).sum;
								}else{
									c = getCell(r, d);
									c.setCellValue(saldo.doubleValue());
									c.setCellStyle(csValue);
								}
							}
							ca.add(Calendar.DATE, 1);
						}
						
						c = getCell(r, Days+2);
						c.setCellValue(saldo.doubleValue());
						c.setCellStyle(csValue);
						row++;
			}
			//footer
			r = getRow(sheet, row);
			c = getCell(r, 0);
			c.setCellValue("Итого");
			c.setCellStyle(csNameB);			

			Calendar ca = Calendar.getInstance();
			ca.setTime(mperiod.getStartDate());
			
			BigDecimal summm = Env.ZERO;
			
			int count = BanksName.size();
			for(int d = 0; d <= Days+1; d++){
				double footersum = .0;
				for(int e = 1; e <= count; e++){
					Row rr = sheet.getRow((row-e));
					Cell ce = rr.getCell(d+1);
					try{
						double b = ce.getNumericCellValue();
						footersum += b;
					}catch(Exception ee){}
					
				}
				c = getCell(r, d+1);
				if(footersum != 0)
					c.setCellValue(footersum);
				summm = summm.add(new BigDecimal(footersum));
				c.setCellStyle(csValue);
			}
			
		}
		
		
		//--------------------------------------------- 10 -----------------		
		// bank curr cash
		listBankCurrCash = getBanksCurrCash2(mperiod);
		if(listBankCurrCash.size() > 0){
			row++;

			//caption
			r = getRow(sheet, row);
			c = getCell(r, 0);
			c.setCellValue("Денежные средства на текущих счетах всего:");
			setRowStyle(sheet, Days, r, csNameB);
			c.setCellStyle(csNameB);			
			//data
			row++;

			List<String> BanksName = new ArrayList<String>();
			for(int i=0; i < listBankCurrCash.size(); i++){
				if(BanksName.indexOf(listBankCurrCash.get(i).name) == -1)
					BanksName.add(listBankCurrCash.get(i).name);
			}
			
			
			for(int j=0; j < BanksName.size(); j++){
						r = getRow(sheet, row);
						c = getCell(r, 0);
						c.setCellValue("  "+BanksName.get(j));
						c.setCellStyle(csName);
						
						Calendar ca = Calendar.getInstance();
						ca.setTime(mperiod.getStartDate());
						BigDecimal summ = Env.ZERO;
						
						int c_bank_id = 0;
//						System.out.println(BanksName.get(j));
						for(int i=0; i < listBankCurrCash.size(); i++){
//							
//							System.out.println(listBankCurrCash.get(i).name);
//							
							if(BanksName.get(j).equals(listBankCurrCash.get(i).name)){
								c_bank_id = listBankCurrCash.get(i).c_bank_id;
								break;
							}
						}
						
						BigDecimal saldo = getBanksCurrCashSaldo2(mperiod, c_bank_id); 

						if(saldo.compareTo(Env.ZERO) == 0)
							continue;
			
						for(int d = 1; d <= Days+1; d++){
							for(int i=0; i < listBankCurrCash.size(); i++){
								if(BanksName.get(j).equals(listBankCurrCash.get(i).name) && listBankCurrCash.get(i).dateacct.equals(ca.getTime())){
										c = getCell(r, d);
										saldo = saldo.add(listBankCurrCash.get(i).sum);
										c.setCellValue(saldo.doubleValue());
										c.setCellStyle(csValue);
										summ = summ.add(saldo);
								}else{
									c = getCell(r, d);
									c.setCellValue(saldo.doubleValue());								
									c.setCellStyle(csValue);
								}
							}
							ca.add(Calendar.DATE, 1);
						}
						
						c = getCell(r, Days+2);
						c.setCellValue(saldo.doubleValue());
						c.setCellStyle(csValue);						
						row++;
			}
			//footer
			r = getRow(sheet, row);
			c = getCell(r, 0);
			c.setCellValue("Итого");
			c.setCellStyle(csNameB);

			Calendar ca = Calendar.getInstance();
			ca.setTime(mperiod.getStartDate());
			
//			BigDecimal summm = Env.ZERO;
			int count = BanksName.size();
			for(int d = 0; d <= Days+1; d++){
				double footersum = .0;
				for(int e = 1; e <= count; e++){
					Row rr = sheet.getRow((row-e));
					Cell ce = rr.getCell(d+1);
					try{
						double b = ce.getNumericCellValue();
						footersum += b;
					}catch(Exception ee){}
					
				}
				c = getCell(r, d+1);
				if(footersum != 0)
					c.setCellValue(footersum);
				c.setCellStyle(csValue);
			}

		}		

        
		
		
		for(int i=0; i <= Days; i++){
			sheet.autoSizeColumn(i+1);
		}

		
		
		
		wb.setForceFormulaRecalculation(true);
		FileOutputStream fileOut = new FileOutputStream(filePath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		if(!Env.isWindows())
			Util.launchFile(new File(filePath));
		else{
			try {
				String command = "excel \"" + filePath + "\"";
				Runtime.getRuntime().exec("cmd /c start " + command);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return Msg.translate(m_ctx, "Success");

	}
	
    protected  List<line> getPayemnts(MPeriod period, String fi_codes){
        List<line> result = new ArrayList<line>();

        String sql =  
        "    select coalesce(abs(sum(c.payamt))) summ, e.name as ename, c.dateacct    \n" +
        "    from adempiere.c_payment c    \n" +
        "    inner join adempiere.fi_form3explanation e on e.fi_form3explanation_id = c.fi_form3explanation_id    \n" +
        "    inner join adempiere.fi_form3 f on e.fi_form3_id = f.fi_form3_id    \n" +
        "    where f.fi_code = any('{"+fi_codes+"}')    \n" +
        "    and c.dateacct between '"+period.getStartDate()+"' and '"+period.getEndDate()+"'    \n" +
		"	 and c.posted = 'Y'	\n" +        
        "    group by ename, dateacct    \n" +
        
        "	union all	\n"	+
        
        "	SELECT coalesce(abs(sum(l.stmtAmt)),0),	e.name as ename, l.dateacct    	\n"	+
		" 	FROM adempiere.c_bankstatement h	\n"	+
		" 	inner join adempiere.c_bankstatementline l on l.c_bankstatement_id = h.c_bankstatement_id	\n"	+
		" 	inner join adempiere.fi_form3 f on f.fi_form3_id = l.fi_form3_id	\n"	+
        "    inner join adempiere.fi_form3explanation e on e.fi_form3explanation_id = l.fi_form3explanation_id    \n" +
		" 	where l.dateacct between '"+period.getStartDate()+"' and '"+period.getEndDate()+"'    \n" +
		" 	and f.fi_code = any('{"+fi_codes+"}')	\n" +
		"	and h.posted = 'Y'	\n" +	
        "    group by ename, dateacct    \n" 


		;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = DB.prepareStatement(sql, null);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                if(rs.getBigDecimal(1).compareTo(Env.ZERO) == 1){
                	BigDecimal b = rs.getBigDecimal(1);
                	b = b.setScale(2, BigDecimal.ROUND_HALF_UP);
                	if(isThousand && b.doubleValue() != 0){
                		b = b.divide(new BigDecimal(1000));
                		b = b.setScale(0, BigDecimal.ROUND_HALF_UP);
                	}
                	if(!isThousand)
                		b = b.setScale(2, BigDecimal.ROUND_HALF_UP);                	
                    result.add(new line(b, rs.getString(2), rs.getDate(3)));
                }
            }
        } catch (SQLException e) {
            s_log.log(Level.SEVERE, "BCS_ACReport", e);
        } finally {
            DB.close(rs, pstmt);
            rs = null;
            pstmt = null;
        }
        
        
        return result;
    }    
	
	protected void setRowStyle(XSSFSheet sheet, int Days, Row r, CellStyle cs){
		
		Cell c = null;
		
		for(int i=0; i <= Days+1; i++){
			c = r.createCell(i+1);
			c.setCellStyle(cs);
		}

	}

	protected  BigDecimal getBanksCurrCashSaldo1(MPeriod period, int c_bank_id){
		BigDecimal result = Env.ZERO;
		
		String sql =  
		"	select coalesce(sum(f.amtacctdr - f.amtacctcr),0) from adempiere.fact_acct f	\n" +  
		"	inner join adempiere.c_elementvalue c on c.c_elementvalue_id = f.account_id	\n" +
		"	left join adempiere.c_bankaccount ba on ba.c_bankaccount_id = c.c_bankaccount_id 	\n" + 
		"	inner join adempiere.c_bank b on b.c_bank_id = ba.c_bank_id	\n" +
		"	where value like '1040%'"	+// any('{104001,104002,104003,104004}')	\n" +
		"	and f.dateacct < '"+period.getStartDate()+"'	\n" +
		"	and b.c_bank_id = " + c_bank_id  
		//"	group by b.name, f.dateacct, b.c_bank_id	\n" 
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				if(rs.getBigDecimal(1).compareTo(Env.ZERO) == 1){
                	BigDecimal b = rs.getBigDecimal(1);
                	if(isThousand && b.doubleValue() != 0){
                		b = b.divide(new BigDecimal(1000));
                		b = b.setScale(0, BigDecimal.ROUND_HALF_UP);
                	}
                	if(!isThousand)
                		b = b.setScale(2, BigDecimal.ROUND_HALF_UP);
					result = b;
				}
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		
		return result;
	}	
	
	protected  BigDecimal getBanksCurrCashSaldo2(MPeriod period, int c_bank_id){
		BigDecimal result = Env.ZERO;
		
		String sql =  
		"	select coalesce(sum(f.amtacctdr - f.amtacctcr),0) from adempiere.fact_acct f	\n" +  
		"	inner join adempiere.c_elementvalue c on c.c_elementvalue_id = f.account_id	\n" +
		"	left join adempiere.c_bankaccount ba on ba.c_bankaccount_id = c.c_bankaccount_id 	\n" + 
		"	inner join adempiere.c_bank b on b.c_bank_id = ba.c_bank_id	\n" +
		"	where value like '1040%'"	+// any('{104001,104002,104003,104004}')	\n" +
		"	and f.dateacct <= '"+period.getStartDate()+"'	\n" +
		"	and b.c_bank_id = " + c_bank_id  
		//"	group by b.name, f.dateacct, b.c_bank_id	\n" 
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				if(rs.getBigDecimal(1).compareTo(Env.ZERO) == 1){
                	BigDecimal b = rs.getBigDecimal(1);
                	if(isThousand && b.doubleValue() != 0){
                		b = b.divide(new BigDecimal(1000));
                		b = b.setScale(0, BigDecimal.ROUND_HALF_UP);
                	}

                	if(!isThousand)
                		b = b.setScale(2, BigDecimal.ROUND_HALF_UP);
					result = b;
				}
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		
		return result;
	}	
	
	protected  List<line> getBanksCurrCash1(MPeriod period){
		List<line> result = new ArrayList<line>();
		
		Calendar c = Calendar.getInstance();
		
		c.setTime(period.getStartDate());
//		c.add(Calendar.DATE, -1);
		Date d1 = c.getTime();
		
		c.setTime(period.getEndDate());
		c.add(Calendar.DATE, 1);
		Date d2 = c.getTime();
		
		String sql =  
		"	select sum(f.amtacctdr - f.amtacctcr), (b.name) as name, f.dateacct, b.c_bank_id  from adempiere.fact_acct f	\n" +
//				"	select sum(f.amtacctdr - f.amtacctcr), (coalesce(b.name,'') ||' - ' ||coalesce(ba.accountno,'')) as name, f.dateacct, b.c_bank_id  from adempiere.fact_acct f	\n" +		
		"	inner join adempiere.c_elementvalue c on c.c_elementvalue_id = f.account_id	\n" +
		"	left join adempiere.c_bankaccount ba on ba.c_bankaccount_id = c.c_bankaccount_id 	\n" + 
		"	inner join adempiere.c_bank b on b.c_bank_id = ba.c_bank_id	\n" +
		"	where value like '1040%'	\n"	+// any('{104001,104002,104003,104004}')	\n" +
//		"	and f.dateacct between '"+d1+"' and '"+d2+"'	\n" +
		"	and f.dateacct < '"+d2+"'	\n" +
		"	group by b.name, ba.accountno, f.dateacct, b.c_bank_id	\n" 
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
//				if(rs.getBigDecimal(1).compareTo(Env.ZERO) == 1){
                	BigDecimal b = rs.getBigDecimal(1);

                	if(isThousand && b.doubleValue() != 0){
                		b = b.divide(new BigDecimal(1000));
                		b = b.setScale(0, BigDecimal.ROUND_HALF_UP);
                	}

                	if(!isThousand)
                		b = b.setScale(2, BigDecimal.ROUND_HALF_UP);
                	
                	line l = new line(b, rs.getString(2), rs.getDate(3));
                	l.c_bank_id = rs.getInt(4);
					result.add(l);
//					result.add(new line(b, rs.getString(2), rs.getDate(3)));
//				}
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		
		return result;
	}	

	protected  List<line> getBanksCurrCash2(MPeriod period){
		List<line> result = new ArrayList<line>();
		
		String sql =  
		"	select sum(f.amtacctdr - f.amtacctcr), (b.name) as name, f.dateacct, b.c_bank_id  from adempiere.fact_acct f	\n" +  
//				"	select sum(f.amtacctdr - f.amtacctcr), (coalesce(b.name,'') ||' - ' ||coalesce(ba.accountno,'')) as name, f.dateacct, b.c_bank_id  from adempiere.fact_acct f	\n" +
		"	inner join adempiere.c_elementvalue c on c.c_elementvalue_id = f.account_id	\n" +
		"	left join adempiere.c_bankaccount ba on ba.c_bankaccount_id = c.c_bankaccount_id 	\n" + 
		"	inner join adempiere.c_bank b on b.c_bank_id = ba.c_bank_id	\n" +
		"	where value like '1040%'	\n"	+// any('{104001,104002,104003,104004}')	\n" +
//		"	and f.dateacct between '"+period.getStartDate()+"' and '"+period.getEndDate()+"'	\n" +
		"	and f.dateacct < '"+period.getEndDate()+"'	\n" +
		"	group by b.name,ba.accountno, f.dateacct, b.c_bank_id	\n" 
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
//				if(rs.getBigDecimal(1).compareTo(Env.ZERO) == 1){
                	BigDecimal b = rs.getBigDecimal(1);
                	if(isThousand && b.doubleValue() != 0){
                		b = b.divide(new BigDecimal(1000));
                		b = b.setScale(0, BigDecimal.ROUND_HALF_UP);
                	}

                	if(!isThousand)
                		b = b.setScale(2, BigDecimal.ROUND_HALF_UP);
                	
                	line l = new line(b, rs.getString(2), rs.getDate(3));
                	l.c_bank_id = rs.getInt(4);
					result.add(l);
//					result.add(new line(b, rs.getString(2), rs.getDate(3)));
//				}
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		
		return result;
	}	
	
	protected  BigDecimal getBanksDepositSaldo1(MPeriod period, int c_bank_id){
		BigDecimal result = Env.ZERO;
		
		String sql =  
		"	select coalesce(sum(f.amtacctdr - f.amtacctcr),0) from adempiere.fact_acct f	\n" +  
		"	inner join adempiere.c_elementvalue c on c.c_elementvalue_id = f.account_id	\n" +
		"	left join adempiere.c_bankaccount ba on ba.c_bankaccount_id = c.c_bankaccount_id 	\n" + 
		"	inner join adempiere.c_bank b on b.c_bank_id = ba.c_bank_id	\n" +
		"	where value like '1060%' " + //any('{106001,106002,106003,106004,106005,106006,106007,106008}')	\n" +
		"	and f.dateacct < '"+period.getStartDate()+"'	\n" +
		"	and b.c_bank_id = " + c_bank_id  
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				if(rs.getBigDecimal(1).compareTo(Env.ZERO) == 1){
                	BigDecimal b = rs.getBigDecimal(1);
                	if(isThousand && b.doubleValue() != 0){
                		b = b.divide(new BigDecimal(1000));
                		b = b.setScale(0, BigDecimal.ROUND_HALF_UP);
                	}

                	if(!isThousand)
                		b = b.setScale(2, BigDecimal.ROUND_HALF_UP);
					result = b;
				}
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		return result;
	}
	
	protected  BigDecimal getBanksDepositSaldo2(MPeriod period, int c_bank_id){
		BigDecimal result = Env.ZERO;
		
		String sql =  
		"	select coalesce(sum(f.amtacctdr - f.amtacctcr),0) from adempiere.fact_acct f	\n" +  
		"	inner join adempiere.c_elementvalue c on c.c_elementvalue_id = f.account_id	\n" +
		"	left join adempiere.c_bankaccount ba on ba.c_bankaccount_id = c.c_bankaccount_id 	\n" + 
		"	inner join adempiere.c_bank b on b.c_bank_id = ba.c_bank_id	\n" +
		"	where value like '1060%' " + //any('{106001,106002,106003,106004,106005,106006,106007,106008}')	\n" +
		"	and f.dateacct <= '"+period.getStartDate()+"'	\n" +
		"	and b.c_bank_id = " + c_bank_id  
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				if(rs.getBigDecimal(1).compareTo(Env.ZERO) == 1){
                	BigDecimal b = rs.getBigDecimal(1);
                	if(isThousand && b.doubleValue() != 0){
                		b = b.divide(new BigDecimal(1000));
                		b = b.setScale(0, BigDecimal.ROUND_HALF_UP);
                	}

                	if(!isThousand)
                		b = b.setScale(2, BigDecimal.ROUND_HALF_UP);
					result = b;
				}
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		return result;
	}
	

	protected  List<line> getBanksDeposit1(MPeriod period){
		List<line> result = new ArrayList<line>();
		
		Calendar c = Calendar.getInstance();
		
		c.setTime(period.getStartDate());
		c.add(Calendar.DATE, -1);
		Date d1 = c.getTime();
		
		c.setTime(period.getEndDate());
		c.add(Calendar.DATE, 1);
		Date d2 = c.getTime();

		
		String sql =  
		"	select sum(f.amtacctdr - f.amtacctcr), (b.name) as name, f.dateacct, b.c_bank_id  from adempiere.fact_acct f	\n" +  
		"	inner join adempiere.c_elementvalue c on c.c_elementvalue_id = f.account_id	\n" +
		"	left join adempiere.c_bankaccount ba on ba.c_bankaccount_id = c.c_bankaccount_id 	\n" + 
		"	inner join adempiere.c_bank b on b.c_bank_id = ba.c_bank_id	\n" +
		"	where value like '1060%'	\n" + //any('{106001,106002,106003,106004,106005,106006,106007,106008}')	\n" +
//		"	and f.dateacct between '"+d1+"' and '"+d2+"'	\n" +
		"	and f.dateacct < '"+d2+"'	\n" +
		"	group by b.name, ba.accountno, f.dateacct, b.c_bank_id	\n" 
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
//				if(rs.getBigDecimal(1).compareTo(Env.ZERO) == 1){
                	BigDecimal b = rs.getBigDecimal(1);
                	if(isThousand && b.doubleValue() != 0){
                		b = b.divide(new BigDecimal(1000));
                		b = b.setScale(0, BigDecimal.ROUND_HALF_UP);
                	}

                	if(!isThousand)
                		b = b.setScale(2, BigDecimal.ROUND_HALF_UP);
                	line l = new line(b, rs.getString(2), rs.getDate(3));
                	l.c_bank_id = rs.getInt(4);
					result.add(l);
//				}
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		
		return result;
	}
	
	protected  List<line> getBanksDeposit3(MPeriod period){
		List<line> result = new ArrayList<line>();
		
		Calendar c = Calendar.getInstance();
		
		c.setTime(period.getStartDate());
		c.add(Calendar.DATE, -1);
		Date d1 = c.getTime();
		
		c.setTime(period.getEndDate());
		c.add(Calendar.DATE, 1);
		Date d2 = c.getTime();

		
		String sql =  
		"	select sum(f.amtacctdr), (b.name) as name, f.dateacct, b.c_bank_id  from adempiere.fact_acct f	\n" +  
		"	inner join adempiere.c_elementvalue c on c.c_elementvalue_id = f.account_id	\n" +
		"	left join adempiere.c_bankaccount ba on ba.c_bankaccount_id = c.c_bankaccount_id 	\n" + 
		"	inner join adempiere.c_bank b on b.c_bank_id = ba.c_bank_id	\n" +
		"	where value like '1060%'	\n" + //any('{106001,106002,106003,106004,106005,106006,106007,106008}')	\n" +
		"	and f.dateacct between '"+d1+"' and '"+d2+"'	\n" +
//		"	and f.dateacct < '"+d2+"'	\n" +
		"	group by b.name, ba.accountno, f.dateacct, b.c_bank_id	\n" 
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
//				if(rs.getBigDecimal(1).compareTo(Env.ZERO) == 1){
                	BigDecimal b = rs.getBigDecimal(1);
                	if(isThousand && b.doubleValue() != 0){
                		b = b.divide(new BigDecimal(1000));
                		b = b.setScale(0, BigDecimal.ROUND_HALF_UP);
                	}

                	if(!isThousand)
                		b = b.setScale(2, BigDecimal.ROUND_HALF_UP);
                	line l = new line(b, rs.getString(2), rs.getDate(3));
                	l.c_bank_id = rs.getInt(4);
					result.add(l);
//				}
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		
		return result;
	}

	protected  List<line> getBanksDeposit4(MPeriod period){
		List<line> result = new ArrayList<line>();
		
		Calendar c = Calendar.getInstance();
		
		c.setTime(period.getStartDate());
		c.add(Calendar.DATE, -1);
		Date d1 = c.getTime();
		
		c.setTime(period.getEndDate());
		c.add(Calendar.DATE, 1);
		Date d2 = c.getTime();

		
		String sql =  
		"	select sum(f.amtacctcr), (b.name) as name, f.dateacct, b.c_bank_id  from adempiere.fact_acct f	\n" +  
		"	inner join adempiere.c_elementvalue c on c.c_elementvalue_id = f.account_id	\n" +
		"	left join adempiere.c_bankaccount ba on ba.c_bankaccount_id = c.c_bankaccount_id 	\n" + 
		"	inner join adempiere.c_bank b on b.c_bank_id = ba.c_bank_id	\n" +
		"	where value like '1060%'	\n" + //any('{106001,106002,106003,106004,106005,106006,106007,106008}')	\n" +
		"	and f.dateacct between '"+d1+"' and '"+d2+"'	\n" +
//		"	and f.dateacct < '"+d2+"'	\n" +
		"	group by b.name, ba.accountno, f.dateacct, b.c_bank_id	\n" 
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
//				if(rs.getBigDecimal(1).compareTo(Env.ZERO) == 1){
                	BigDecimal b = rs.getBigDecimal(1);
                	if(isThousand && b.doubleValue() != 0){
                		b = b.divide(new BigDecimal(1000));
                		b = b.setScale(0, BigDecimal.ROUND_HALF_UP);
                	}

                	if(!isThousand)
                		b = b.setScale(2, BigDecimal.ROUND_HALF_UP);
                	line l = new line(b, rs.getString(2), rs.getDate(3));
                	l.c_bank_id = rs.getInt(4);
					result.add(l);
//				}
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		
		return result;
	}
	
	
	protected  List<line> getBanksDeposit2(MPeriod period){
		List<line> result = new ArrayList<line>();
		
		String sql =  
		"	select sum(f.amtacctdr - f.amtacctcr), (b.name) as name, f.dateacct, b.c_bank_id  from adempiere.fact_acct f	\n" +  
		"	inner join adempiere.c_elementvalue c on c.c_elementvalue_id = f.account_id	\n" +
		"	left join adempiere.c_bankaccount ba on ba.c_bankaccount_id = c.c_bankaccount_id 	\n" + 
		"	inner join adempiere.c_bank b on b.c_bank_id = ba.c_bank_id	\n" +
		"	where value like '1060%' 	\n" + //any('{106001,106002,106003,106004,106005,106006,106007,106008}')	\n" +
//		"	and f.dateacct between '"+period.getStartDate()+"' and '"+period.getEndDate()+"'	\n" +
		"	and f.dateacct < '"+period.getEndDate()+"'	\n" +
		"	group by b.name, ba.accountno, f.dateacct, b.c_bank_id	\n" 
		;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			while(rs.next()) {
//				if(rs.getBigDecimal(1).compareTo(Env.ZERO) == 1){
                	BigDecimal b = rs.getBigDecimal(1);
                	if(isThousand && b.doubleValue() != 0){
                		b = b.divide(new BigDecimal(1000));
                		b = b.setScale(0, BigDecimal.ROUND_HALF_UP);
                	}

                	if(!isThousand)
                		b = b.setScale(2, BigDecimal.ROUND_HALF_UP);
                	line l = new line(b, rs.getString(2), rs.getDate(3));
                	l.c_bank_id = rs.getInt(4);
					result.add(l);
//				}
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "BCS_ACReport", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		
		return result;
	}
	

	protected String excNull(Object o){
		String result = "";
			if(o != null && o instanceof String)
				result = (String) o;
			
		return result;
	}
	
	protected Row getRow(XSSFSheet sheet, int row){
		Row result = null;
		
		if(sheet != null){
			Row r = sheet.getRow(row);
			if(r == null)
				r = sheet.createRow(row);
			
			result = r;
		}
		
		return result;
	}	
	
	protected Cell getCell(Row r, int col){
		Cell result = null;
		if(r!=null){
			Cell c = r.getCell(col);
			if(c == null)
				c = r.createCell(col);
			
			result = c;
		}
		
		return result;
	}
}


class line{
	BigDecimal sum = Env.ZERO;
	String name = "";
	Date dateacct = null;
	int c_bank_id = 0;
	line(BigDecimal sum, String name, Date dateacct){
		this.sum = sum;
		this.name = name;
		this.dateacct = dateacct;
		
	}
	
	
}

